package edu.ucsd.cse135.gas.logic.chair;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import edu.ucsd.cse135.gas.logic.Reviewer;
import edu.ucsd.cse135.gas.logic.User;
import edu.ucsd.cse135.gas.logic.applicant.Applicant;
import edu.ucsd.cse135.gas.logic.support.Database;

public class ReviewProcess {

	public static boolean isStarted() {
		
		try 
		{
			Connection conn = Database.open();
			
			String sql = "SELECT value FROM review_process WHERE key = 'started';";
			
			PreparedStatement preStmt = conn.prepareStatement(sql);
			ResultSet rs = preStmt.executeQuery();
			
			rs.next();
			
			if(rs.getString("value").equals("false"))
			{
				return false;
			}
			else
			{
				return true;
			}
			
		}
		catch(Exception e)
		{
			return true;
		}
		
	}
	
	public static boolean start() {
		
		try 
		{
			Connection conn = Database.open();
			
			String sql = "SELECT COUNT(mail) AS count FROM user_role WHERE role = 'reviewer';";
			
			PreparedStatement preStmt = conn.prepareStatement(sql);
			ResultSet rs = preStmt.executeQuery();
			
			rs.next();
			
			if(rs.getInt("count") >= 2)
			{
			
				sql = "SELECT mail FROM applicant;";
						
				preStmt = conn.prepareStatement(sql);
				rs = preStmt.executeQuery();
				
				ArrayList<String> applicants = new ArrayList<String>();
				
				while(rs.next())
				{
					applicants.add(rs.getString("mail"));
				}
				
				sql = "SELECT mail FROM user_role WHERE role = 'reviewer';";
				
				preStmt = conn.prepareStatement(sql);
				rs = preStmt.executeQuery();
				
				ArrayList<String> reviewers = new ArrayList<String>();
			
				while(rs.next())
				{
					reviewers.add(rs.getString("mail"));
				}
				
				sql = "INSERT INTO reviewer_applicant (reviewer, applicant, graded, comment) VALUES (?, ?, 0, ' ');";
				
				preStmt = conn.prepareStatement(sql);
				
				int reviewersCounter = 0;
				
				for(int i = 0; i < applicants.size(); i++)
				{
						preStmt.setString(1, reviewers.get(reviewersCounter));
						preStmt.setString(2, applicants.get(i));
						preStmt.executeUpdate();
					
					if(reviewersCounter == reviewers.size()-1)
					{
						reviewersCounter = 0;
					}
					else
					{
						reviewersCounter++;
					}
				}
				
				sql = "UPDATE review_process SET value = 'true' WHERE key = 'started';";
				
				preStmt = conn.prepareStatement(sql);
				preStmt.executeUpdate();
				
				preStmt.close();
				rs.close();
				Database.close();
				
				return true;
			}
			else
			{
				return false;
			}
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return false;
		}
		
	}
	
	public static ArrayList<Reviewer> getReviewers() {
		
		try
		{
			Connection conn = Database.open();
			
			String sql = "SELECT user_role.mail, " +
						 "(SELECT COUNT(id) FROM reviewer_applicant WHERE graded = 1 AND reviewer = user_role.mail) AS graded, " +
						 "(SELECT COUNT(id) FROM reviewer_applicant WHERE graded = 0 AND reviewer = user_role.mail) AS ungraded " +
						 "FROM reviewer_applicant, user_role WHERE user_role.role = 'reviewer' GROUP BY user_role.mail;";
			
			PreparedStatement preStmt = conn.prepareStatement(sql);
			ResultSet rs = preStmt.executeQuery();
			
			ArrayList<Reviewer> reviewers = new ArrayList<Reviewer>();
			
			while(rs.next())
			{
				Reviewer reviewer = new Reviewer();
				
				reviewer.setMail(rs.getString("mail"));
				reviewer.setGraded(rs.getString("graded"));
				reviewer.setUngraded(rs.getString("ungraded"));
				
				reviewers.add(reviewer);
			}
			
			return reviewers;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return null;
		}
	}
	
	public static ArrayList<Reviewer> getReviewersBlank() {
		
		try
		{
			Connection conn = Database.open();
			
			String sql = "SELECT mail FROM user_role WHERE role = 'reviewer';";
			
			PreparedStatement preStmt = conn.prepareStatement(sql);
			ResultSet rs = preStmt.executeQuery();
			
			ArrayList<Reviewer> reviewers = new ArrayList<Reviewer>();
			
			while(rs.next())
			{
				Reviewer reviewer = new Reviewer();
				
				reviewer.setMail(rs.getString("mail"));
				
				reviewers.add(reviewer);
			}
			
			return reviewers;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return null;
		}
	}
	
	public static void addReviewer(String mail, String password) {
		
		try
		{
			Connection conn = Database.open();
			
			String sql = "INSERT INTO users (mail, password) VALUES (?, md5(?));";
				
			PreparedStatement preStmt = conn.prepareStatement(sql);
			preStmt.setString(1, mail);
			preStmt.setString(2, password);
			
			preStmt.executeUpdate();
			
			sql = "INSERT INTO user_role (mail, role) VALUES (?, 'reviewer');";
			
			preStmt = conn.prepareStatement(sql);
			preStmt.setString(1, mail);
			
			preStmt.executeUpdate();
			
			preStmt.close();
			Database.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
	}
	
	public static void deleteReviewer(String mail) {
		
		try
		{
			Connection conn = Database.open();
			
			String sql = "DELETE FROM users WHERE mail = ?;";
			
			PreparedStatement preStmt = conn.prepareStatement(sql);
			preStmt.setString(1, mail);
			
			preStmt.executeUpdate();
			
			preStmt.close();
			Database.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}

	public static ArrayList<Applicant> ungradedApplications(String reviewer) {
		
		try {
		
			Connection conn = Database.open();
			
			String sql = "SELECT application_status, first_name, name, mail FROM applicant, reviewer_applicant WHERE graded = 0 AND reviewer = ?;";
			
			PreparedStatement preStmt = conn.prepareStatement(sql);
			preStmt.setString(1, reviewer);
			
			ResultSet rs = preStmt.executeQuery();
			
			ArrayList<Applicant> applicants = new ArrayList<Applicant>();
			
			while(rs.next())
			{
				Applicant applicant = new Applicant();
				
				applicant.setApplicationStatus(rs.getString("application_status"));
				applicant.setFirstName(rs.getString("first_name"));
				applicant.setName(rs.getString("name"));
				applicant.setMail(rs.getString("mail"));
				
				applicants.add(applicant);
			}
			
			return applicants;
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return null;
		}
		
	}
	
	public static ArrayList<Applicant> gradedApplications(String reviewer) {
		
		try {
			
			Connection conn = Database.open();
			
			String sql = "SELECT first_name, name, application_status, mail FROM applicant, reviewer_applicant WHERE graded != 0 AND reviewer = ?;";
			
			PreparedStatement preStmt = conn.prepareStatement(sql);
			preStmt.setString(1, reviewer);
			
			ResultSet rs = preStmt.executeQuery();
			
			ArrayList<Applicant> applicants = new ArrayList<Applicant>();
			
			while(rs.next())
			{
				Applicant applicant = new Applicant();
				
				applicant.setName(rs.getString("name"));
				applicant.setFirstName(rs.getString("first_name"));
				applicant.setApplicationStatus(rs.getString("application_status"));
				applicant.setMail(rs.getString("mail"));
				
				applicants.add(applicant);
			}
			
			return applicants;
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return null;
		}
		
	}
}